﻿using QRCodeBluetooth.Helper;
using QRCodeBluetooth.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace QRCodeBluetooth.DAL
{
    public class ProjectDAL
    {
        public bool AddProject(ProjectInfo projectInfo)
        {
            string strsql = string.Format(@"INSERT INTO project_info(project_code,project_name,project_addr,project_ip,project_key)VALUES('{0}','{1}','{2}','{3}','{4}')"
                                          , projectInfo.project_code, projectInfo.project_name, projectInfo.project_addr, projectInfo.project_ip, projectInfo.project_key);
            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }

        public bool EditProject(ProjectInfo projectInfo)
        {
            string strsql = string.Format(@"UPDATE project_info SET project_code='{0}',project_name='{1}',project_addr='{2}',project_ip='{3}',project_key='{4}' WHERE id={5}"
                                          , projectInfo.project_code, projectInfo.project_name, projectInfo.project_addr, projectInfo.project_ip, projectInfo.project_key, projectInfo.id);
            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }

        public bool DeleteProject(ProjectInfo projectInfo)
        {
            string strsql = string.Format(@"DELETE FROM project_info WHERE id={0}", projectInfo.id);
            return SqlHelper<ProjectInfo>.SqlExecute(strsql);
        }

        public List<ProjectInfo> QueryProject(ProjectInfo projectInfo)
        {
            string strsql = "SELECT * FROM project_info";

            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(projectInfo.project_code))
            {
                sqlwhere += " project_code '%" + projectInfo.project_code + "%' AND";
            }
            if (!string.IsNullOrEmpty(projectInfo.project_name))
            {
                sqlwhere += " project_name like '%" + projectInfo.project_name + "%' AND";
            }
            if (!string.IsNullOrEmpty(projectInfo.project_addr))
            {
                sqlwhere += " project_addr like '%" + projectInfo.project_addr + "%' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strsql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (projectInfo.Recordhead > -1 && projectInfo.Recordtail > 0)
            {
                strsql += " LIMIT " + projectInfo.Recordhead + "," + projectInfo.Recordtail;
            }

            return SqlHelper<ProjectInfo>.SqlList(strsql);
        }

        public int QueryProjectCount(ProjectInfo projectInfo)
        {
            string strsql = "SELECT COUNT(1) FROM project_info";

            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(projectInfo.project_code))
            {
                sqlwhere += " project_code '%" + projectInfo.project_code + "%' AND";
            }
            if (!string.IsNullOrEmpty(projectInfo.project_name))
            {
                sqlwhere += " project_name like '%" + projectInfo.project_name + "%' AND";
            }
            if (!string.IsNullOrEmpty(projectInfo.project_addr))
            {
                sqlwhere += " project_addr like '%" + projectInfo.project_addr + "%' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strsql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            return SqlHelper<ProjectInfo>.SqlScalar(strsql);
        }

        public int CheckReg(string regcode)
        {
            string strsql = "select COUNT(1) FROM area WHERE RegCode='" + regcode + "'";
            return SqlHelper<ProjectInfo>.SqlScalar(strsql);
        }

        public ProjectInfo LoadProjectInfo(int projectid)
        {
            string strsql = "SELECT * FROM project_info where id=" + projectid;

            return SqlHelper<ProjectInfo>.SqlSingleData(strsql);
        }
    }
}
